﻿using System;
using System.Collections.Generic;
using System.Text;
using X.DataFactory;
using X.Entity;
using System.Data;

namespace X.Data.Access
{
    public class Params : IParams
    {
        public Entity.SiteInfo GetSiteInfo()
        {
            string sSQL = @"SELECT XKey,XValue 
                            FROM X_Params 
                            WHERE XKey IN('SiteName','SiteUrl','SiteTitle','SiteKeyword','SiteDescription')";

            SiteInfo siteInfo = new SiteInfo();

            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(sSQL))
            {
                while (reader.Read())
                {
                    switch (reader.GetString("XKey"))
                    {
                        case X.Common.XConst.SITE_Name:
                            siteInfo.SiteName = reader.GetString("XValue");
                            break;
                        case X.Common.XConst.SITE_Url:
                            siteInfo.SiteUrl = reader.GetString("XValue");
                            break;
                        case X.Common.XConst.SITE_Title:
                            siteInfo.SiteTitle = reader.GetString("XValue");
                            break;
                        case X.Common.XConst.SITE_Keyword:
                            siteInfo.SiteKeyword = reader.GetString("XValue");
                            break;
                        case X.Common.XConst.SITE_Description:
                            siteInfo.SiteDescription = reader.GetString("XValue");
                            break;
                    }
                }
            }

            return siteInfo;
        }

        public bool UpdataSiteInfo(Entity.SiteInfo siteInfo)
        {
            string sSQL = "UPDATE X_Params SET XValue = @XValue WHERE XKey = 'SiteName'";

            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@XValue", DbType.String, siteInfo.SiteName);
            DBHelper.ExecuteSql(sSQL, cmdParams);

            sSQL = "UPDATE X_Params SET XValue = @XValue WHERE XKey = 'SiteUrl'";
            cmdParams = new Parameters();
            cmdParams.AddInParameter("@XValue", DbType.String, siteInfo.SiteUrl);
            DBHelper.ExecuteSql(sSQL, cmdParams);

            sSQL = "UPDATE X_Params SET XValue = @XValue WHERE XKey = 'SiteTitle'";
            cmdParams = new Parameters();
            cmdParams.AddInParameter("@XValue", DbType.String, siteInfo.SiteTitle);
            DBHelper.ExecuteSql(sSQL, cmdParams);

            sSQL = "UPDATE X_Params SET XValue = @XValue WHERE XKey = 'SiteKeyword'";
            cmdParams = new Parameters();
            cmdParams.AddInParameter("@XValue", DbType.String, siteInfo.SiteKeyword);
            DBHelper.ExecuteSql(sSQL, cmdParams);

            sSQL = "UPDATE X_Params SET XValue = @XValue WHERE XKey = 'SiteDescription'";
            cmdParams = new Parameters();
            cmdParams.AddInParameter("@XValue", DbType.String, siteInfo.SiteDescription);
            DBHelper.ExecuteSql(sSQL, cmdParams);

            return true;
        }
    }

    public class Msn : IMsn
    {
        private MsnInfo MsnFromReader(NullableDataReader reader)
        {
            MsnInfo msnInfo = new MsnInfo();

            msnInfo.Id = reader.GetInt32("Id");
            msnInfo.MsnAccount = reader.GetString("MsnAccount");
            msnInfo.MsnUsername = reader.GetString("MsnUsername");
            msnInfo.IsAvailable = reader.GetBoolean("IsAvailable");
            msnInfo.AccountType = reader.GetInt32("AccountType");

            return msnInfo;
        }

        public MsnInfo GetMsnInfo(int id)
        {
            string sSQL = "SELECT * FROM X_Msn WHERE Id = @Id";

            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@Id", DbType.Int32, id);

            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(sSQL, cmdParams))
            {
                while (reader.Read())
                {
                    return MsnFromReader(reader);
                }
            }

            return new MsnInfo(true);
        }

        public IList<MsnInfo> GetAvaList()
        {
            string sSQL = "SELECT * FROM X_Msn WHERE IsAvailable = true";

            IList<MsnInfo> list = new List<MsnInfo>();

            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(sSQL))
            {
                while (reader.Read())
                {
                    list.Add(MsnFromReader(reader));
                }
            }

            return list;
        }

        public IList<MsnInfo> GetAllList()
        {
            string sSQL = "SELECT * FROM X_Msn";

            IList<MsnInfo> list = new List<MsnInfo>();

            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(sSQL))
            {
                while (reader.Read())
                {
                    list.Add(MsnFromReader(reader));
                }
            }

            return list;
        }

        public bool Update(MsnInfo msnInfo)
        {
            string sSQL = @"UPDATE X_Msn 
                            SET MsnAccount = @MsnAccount,MsnUsername = @MsnUsername,IsAvailable = @IsAvailable,AccountType = @AccountType 
                            WHERE Id = @Id";

            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@MsnAccount", DbType.String, msnInfo.MsnAccount);
            cmdParams.AddInParameter("@MsnUsername", DbType.String, msnInfo.MsnUsername);
            cmdParams.AddInParameter("@IsAvailable", DbType.Boolean, msnInfo.IsAvailable);
            cmdParams.AddInParameter("@AccountType", DbType.Int32, msnInfo.AccountType);
            cmdParams.AddInParameter("@Id", DbType.Int32, msnInfo.Id);

            return DBHelper.ExecuteSql(sSQL, cmdParams);
        }

        public bool Create(MsnInfo msnInfo)
        {
            string sSQL = @"INSERT INTO X_Msn(MsnAccount,MsnUserName,IsAvailable,AccountType)
                            VALUES(@MsnAccount,@MsnUserName,@IsAvailable,@AccountType)";

            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@MsnAccount", DbType.String, msnInfo.MsnAccount);
            cmdParams.AddInParameter("@MsnUsername", DbType.String, msnInfo.MsnUsername);
            cmdParams.AddInParameter("@IsAvailable", DbType.Boolean, msnInfo.IsAvailable);
            cmdParams.AddInParameter("@AccountType", DbType.Int32, msnInfo.AccountType);

            return DBHelper.ExecuteSql(sSQL, cmdParams);
        }

        public bool Delete(int id)
        {
            string sSQL = "DELETE FROM X_Msn WHERE Id = @Id";

            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@Id", DbType.Int32, id);

            return DBHelper.ExecuteSql(sSQL, cmdParams);
        }
    }

    public class Ad : IAd
    {
        public bool Create(AdInfo adInfo)
        {
            string sSQL = @"INSERT INTO X_Ad(AdName,AdLink,AdImage,OrderNum,IsShow)
                            VALUES(@AdName,@AdLink,@AdImage,@OrderNum,@IsShow)";

            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@AdName", DbType.String, adInfo.AdName);
            cmdParams.AddInParameter("@AdLink", DbType.String, adInfo.AdLink);
            cmdParams.AddInParameter("@AdImage", DbType.String, adInfo.AdImage);
            cmdParams.AddInParameter("@OrderNum", DbType.Int32, adInfo.OrderNum);
            cmdParams.AddInParameter("@IsShow", DbType.Boolean, adInfo.IsShow);

            return DBHelper.ExecuteSql(sSQL, cmdParams);
        }

        public bool Update(AdInfo adInfo)
        {
            string sSQL = @"UPDATE X_Ad
                            SET AdName = @AdName,AdLink = @AdLink,AdImage = @AdImage,OrderNum = @OrderNum,IsShow = @IsShow
                            WHERE Id = @Id";

            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@AdName", DbType.String, adInfo.AdName);
            cmdParams.AddInParameter("@AdLink", DbType.String, adInfo.AdLink);
            cmdParams.AddInParameter("@AdImage", DbType.String, adInfo.AdImage);
            cmdParams.AddInParameter("@OrderNum", DbType.Int32, adInfo.OrderNum);
            cmdParams.AddInParameter("@IsShow", DbType.Boolean, adInfo.IsShow);
            cmdParams.AddInParameter("@Id", DbType.Int32, adInfo.Id);

            return DBHelper.ExecuteSql(sSQL, cmdParams);
        }

        public bool Delete(int id)
        {
            string sSQL = @"DELETE FROM X_Ad WHERE Id = @Id";

            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@Id", DbType.Int32, id);

            return DBHelper.ExecuteSql(sSQL, cmdParams);
        }

        public IList<AdInfo> GetAllList()
        {
            string sSQL = "SELECT * FROM X_Ad ORDER BY OrderNum";

            IList<AdInfo> list = new List<AdInfo>();

            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(sSQL))
            {
                while (reader.Read())
                {
                    list.Add(AdFromReader(reader));
                }
            }

            return list;
        }

        public IList<AdInfo> GetList(int topCount)
        {
            string sSQL = "SELECT TOP {0} * FROM X_Ad WHERE IsShow = true ORDER BY OrderNum DESC";

            sSQL = string.Format(sSQL, topCount);

            IList<AdInfo> list = new List<AdInfo>();

            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(sSQL))
            {
                while (reader.Read())
                {
                    list.Add(AdFromReader(reader));
                }
            }

            return list;
        }

        public AdInfo GetAdInfo(int id)
        {
            string sSQL = @"SELECT * FROM X_Ad WHERE Id = @Id";

            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@Id", DbType.Int32, id);

            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(sSQL, cmdParams))
            {
                while (reader.Read())
                {
                    return AdFromReader(reader);
                }
            }

            return new AdInfo(true);
        }

        AdInfo AdFromReader(NullableDataReader reader)
        {
            AdInfo adInfo = new AdInfo();

            adInfo.Id = reader.GetInt32("Id");
            adInfo.AdName = reader.GetString("AdName");
            adInfo.AdLink = reader.GetString("AdLink");
            adInfo.AdImage = reader.GetString("AdImage");
            adInfo.OrderNum = reader.GetInt32("OrderNum");
            adInfo.IsShow = reader.GetBoolean("IsShow");

            return adInfo;
        }
    }
}
